package com.apes.crm.callCenter.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Map;

@Component
public class CallCenterDao extends ApesDao {
    @Autowired
    SqlService sqlService;


    /***
     * 功能： 查询客诉报表
     * @param param
     * @return
     */
    public JSONArray queryReport(JSONObject param) {
        String sSQL = "SELECT A.ID,\n" +
                "       (SELECT B.PLATE_NUMBER FROM CRM_CUSTOMER_CAR B WHERE A.CAR_ID = B.ID) PLATE_NUMBER,\n" +
                "       A.CREATE_DATE,\n" +
                "       (SELECT C.NAME\n" +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                "          WHERE A.TRACER_ID = B.ID\n" +
                "            AND C.ID = B.PARTY_ID) TRACER_NAME, A.COMPLAIN_CONTENT,\n" +
                "       DECODE(A.STATE, 'disposing', '客诉处理', 'visiting', '客服回访', 'transStop',\n" +
                "               '停单申请', 'stop', '停单', '无') CURRENT_NODE\n" +
                "  FROM CRM_CUSTOM_COMPLAIN A\n" +
                " WHERE A.STATE NOT IN ('create', 'end', 'delete')\n" +
                "   AND A.DEPT_ID IN\n" +
                "       (SELECT C.ID FROM MAS_DEPT C WHERE C.BRANCH_ID = :BRANCH_ID)\n";

        if( param.getString("companyId") != null &&  param.getJSONArray("companyId").size()!=0){
            sSQL += " AND A.COMPANY_ID IN ("+ translateArrayToString(param.getJSONArray("companyId"), ",") +")";
        }

        sSQL += " ORDER BY A.CREATE_DATE";


        return toJSONArray(this.find(sSQL,param));
    }

    /***
     * 功能： 查询客诉报表(查询最后操作人)
     * @param param
     * @return
     */
    public JSONArray queryReport_findOperate(Map<String, Object> param) {
        String sSQL = "SELECT OPERATE FROM( SELECT  (( SELECT E.NAME FROM AUT_USERS E WHERE E.ID = D.OPERATOR_ID) || '  ' || D.OPERATE) OPERATE" +
                " FROM IR_LOGGER D WHERE D.BUSINESS_KEY = :ID AND D.BUSINESS_TYPE = 'CustomComplain' ORDER BY D.LAST_UPDATED_STAMP) WHERE ROWNUM = 1";
        return toJSONArray(this.find(sSQL,param));
    }


    /***
     * 功能： 查询客诉单投诉走势报表
     * @param param
     * @return
     */
    public JSONArray queryComplainRateReport(JSONObject param) {
        String sSQL = "SELECT C.MONTH, NVL(SUM(DECODE(C.TYPE, 1, C.NUM)), 0) XCMRTS,\n" +
                "       NVL(SUM(DECODE(C.TYPE, 2, C.NUM)), 0) FXCMRTS, NVL(SUM(C.NUM), 0) TSL\n" +
                "  FROM (SELECT TO_CHAR(C.CREATE_DATE, 'yyyy/mm') MONTH, C.COMPLAIN_TYPE TYPE,\n" +
                "                COUNT(1) NUM\n" +
                "           FROM (SELECT A.ID, A.CREATE_DATE,\n" +
                "                         DECODE((SELECT B.DESCRIPTION\n" +
                "                                   FROM MAS_ENUMERATION B\n" +
                "                                  WHERE B.ID = A.COMPLAIN_TYPE), '洗车', 1, '美容', 1,\n" +
                "                                 2) COMPLAIN_TYPE\n" +
                "                    FROM CRM_CUSTOM_COMPLAIN A\n" +
                "                   WHERE A.STATE <> 'delete'\n" +
                "                     AND A.CREATE_DATE >= TO_DATE(:START_DATE, 'YYYY-MM')\n" +
                "                     AND A.CREATE_DATE <\n" +
                "                         ADD_MONTHS(TO_DATE(:END_DATE, 'YYYY-MM'), 1)) C\n" +
                "          GROUP BY TO_CHAR(C.CREATE_DATE, 'yyyy/mm'), C.COMPLAIN_TYPE\n" +
                "          ORDER BY MONTH, TYPE) C\n" +
                " GROUP BY C.MONTH";
        return toJSONArray(this.find(sSQL,param));
    }


    /***
     * 功能： 查询客诉单月度报表
     * @param param
     * @return
     */
    public JSONArray queryComplainMonthReport(JSONObject param) {
        // 非责任客诉
        String sSQL1 = "SELECT C.COMPLAIN_CATEGORY, COUNT(C.ID) NUM, C.DEPT_NAME, C.DEPT_ID, '非责任客诉' RESPONSIBILITY_COMPLAIN, 0 RESPONSIBILITY_COMPLAIN_ID, C.COMPLAIN_CATEGORY_ID\n" +
                "  FROM (SELECT A.ID,\n" +
                "                (SELECT DESCRIPTION\n" +
                "                    FROM MAS_ENUMERATION\n" +
                "                   WHERE ID = A.COMPLAIN_CATEGORY) COMPLAIN_CATEGORY, A.COMPLAIN_CATEGORY COMPLAIN_CATEGORY_ID,\n" +
                "                '非责任客诉' RESPONSIBILITY_COMPLAIN, A.CREATE_DATE,\n" +
                "                ( SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID ) DEPT_NAME, A.DEPT_ID\n" +
                "           FROM CRM_CUSTOM_COMPLAIN A\n" +
                "          WHERE A.CUSTOM_COMPLAIN_TYPE =\n" +
                "                (SELECT B.ID\n" +
                "                   FROM MAS_ENUMERATION B\n" +
                "                  WHERE B.ENUMERATION_TYPE_ID = 'customComplainType'\n" +
                "                    AND B.DESCRIPTION = '投诉')\n" +
                "            AND A.RESPONSIBILITY_COMPLAIN IN\n" +
                "                (SELECT B.ID\n" +
                "                   FROM MAS_ENUMERATION B\n" +
                "                  WHERE B.ENUMERATION_TYPE_ID = 'responsibilityComplain'\n" +
                "                    AND B.DESCRIPTION = '非责任客诉')\n" +
                "            AND A.COMPLAIN_CATEGORY IS NOT NULL) C WHERE TO_CHAR(C.CREATE_DATE,'YYYY-MM') = :DATE \n" +
                " GROUP BY C.DEPT_ID, C.DEPT_NAME, C.COMPLAIN_CATEGORY, C.COMPLAIN_CATEGORY_ID\n" +
                " ORDER BY C.DEPT_ID, C.COMPLAIN_CATEGORY";
        // 责任客诉
        String sSQL2 = "SELECT C.COMPLAIN_CATEGORY, COUNT(C.ID) NUM, C.DEPT_NAME, C.DEPT_ID, '责任客诉' RESPONSIBILITY_COMPLAIN, 1 RESPONSIBILITY_COMPLAIN_ID, C.COMPLAIN_CATEGORY_ID\n" +
                "  FROM (SELECT A.ID,\n" +
                "                (SELECT DESCRIPTION\n" +
                "                    FROM MAS_ENUMERATION\n" +
                "                   WHERE ID = A.COMPLAIN_CATEGORY) COMPLAIN_CATEGORY, A.COMPLAIN_CATEGORY COMPLAIN_CATEGORY_ID,\n" +
                "                '责任客诉' RESPONSIBILITY_COMPLAIN, A.CREATE_DATE,\n" +
                "                ( SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID ) DEPT_NAME, A.DEPT_ID\n" +
                "           FROM CRM_CUSTOM_COMPLAIN A\n" +
                "          WHERE A.CUSTOM_COMPLAIN_TYPE =\n" +
                "                (SELECT B.ID\n" +
                "                   FROM MAS_ENUMERATION B\n" +
                "                  WHERE B.ENUMERATION_TYPE_ID = 'customComplainType'\n" +
                "                    AND B.DESCRIPTION = '投诉')\n" +
                "            AND A.RESPONSIBILITY_COMPLAIN IN\n" +
                "                (SELECT B.ID\n" +
                "                   FROM MAS_ENUMERATION B\n" +
                "                  WHERE B.ENUMERATION_TYPE_ID = 'responsibilityComplain'\n" +
                "                    AND B.DESCRIPTION <> '非责任客诉')\n" +
                "            AND A.COMPLAIN_CATEGORY IS NOT NULL) C WHERE TO_CHAR(C.CREATE_DATE,'YYYY-MM') = :DATE \n" +
                " GROUP BY C.DEPT_ID, C.DEPT_NAME, C.COMPLAIN_CATEGORY, C.COMPLAIN_CATEGORY_ID\n" +
                " ORDER BY C.DEPT_ID, C.COMPLAIN_CATEGORY";
        JSONArray list1 =   toJSONArray(this.find(sSQL1,param));
        list1.addAll( toJSONArray(this.find(sSQL2,param)));
        return list1;
    }

    /***
     * 功能： 查询节点候选人以及签收人
     * @param param
     * @return
     */
    public JSONArray dealCandidate(JSONObject param) {
        // 用于查询所有的候选人以及签收人
        String sSQL = "SELECT TO_CHAR(C.ID_) ID, TO_CHAR(WM_CONCAT(TO_CHAR(NVL(C.ASSIGNEE_NAME, C.USER_NAME)))) USERS\n" +
                "  FROM (SELECT DISTINCT A.ID_,\n" +
                "                         (SELECT NAME\n" +
                "                             FROM AUT_USERS\n" +
                "                            WHERE TO_CHAR(ID) = A.ASSIGNEE_) ASSIGNEE_NAME,\n" +
                "                         (SELECT NAME\n" +
                "                             FROM AUT_USERS\n" +
                "                            WHERE TO_CHAR(ID) = B.USER_ID_) USER_NAME\n" +
                "           FROM ACT_RU_TASK A\n" +
                "           LEFT JOIN ACT_RU_IDENTITYLINK B\n" +
                "             ON B.TASK_ID_ = A.ID_\n" +
                "          WHERE A.PROC_INST_ID_ = :PROCINSTID \n" +
                "            AND (A.ASSIGNEE_ IS NOT NULL OR\n" +
                "                (A.ASSIGNEE_ IS NULL AND B.TYPE_ = 'candidate' AND\n" +
                "                B.USER_ID_ IS NOT NULL))) C\n" +
                " GROUP BY C.ID_";
        return toJSONArray(this.find(sSQL,param));

    }


    /***
     * 功能： 用于查询当前流程上一次的处理信息
     * @param param
     * @return
     */
    public JSONArray dealPreviousInfo(JSONObject param) {
        String sSQL = "SELECT INFO, NAME, TIME, REMARK, OPERATE\n" +
                "  FROM (SELECT TO_CHAR(T.LAST_UPDATED_STAMP, 'YYYY-MM-DD HH24:MI:SS') || ' ' ||\n" +
                "                 (SELECT NAME FROM AUT_USERS WHERE T.OPERATOR_ID = ID) || ' ' ||\n" +
                "                 T.OPERATE || ' ' || T.REMARK INFO,\n" +
                "                (SELECT NAME FROM AUT_USERS WHERE T.OPERATOR_ID = ID) NAME,\n" +
                "                TO_CHAR(T.LAST_UPDATED_STAMP, 'YYYY-MM-DD HH24:MI:SS') TIME,\n" +
                "                T.REMARK, T.OPERATE\n" +
                "           FROM IR_LOGGER T\n" +
                "          WHERE T.BUSINESS_KEY = :BUSINESSKEY" +
                "          AND T.BUSINESS_TYPE = :BUSINESSTYPE\n" +
                "          ORDER BY T.LAST_UPDATED_STAMP DESC)\n";
        return toJSONArray(this.find(sSQL,param));
    }

    /***
     * 功能： 用于查询当前流程上一次的处理信息
     * @param param
     * @return
     */
    public JSONArray dealTimeConsume(JSONObject param) {
        String sSQL = "SELECT T.NAME_, T.TEXT_, T.LONG_ FROM  ACT_RU_VARIABLE T WHERE T.NAME_ in ('suspendDate','activateDate') AND T.PROC_INST_ID_ = :processInstanceId ORDER BY T.ID_ DESC";
        return toJSONArray(this.find(sSQL,param));
    }


    /***
     * 功能： 用于客诉总控查询
     * @param
     * @return
     */
    public JSONArray queryForComplainControl(JSONObject param) {
        String sSQL = "SELECT A.ID, A.INNER_BUSINESS_TYPE,\n" +
                "       TO_CHAR(A.COMPLAIN_CONTENT) COMPLAIN_CONTENT,\n" +
                "       (SELECT CAR_LICENSE FROM fn_customer_expand WHERE ID = A.CUSTOMER_EXPAND_ID) PLATE_NUMBER, " +
//                "       (SELECT B.PLATE_NUMBER FROM CRM_CUSTOMER_CAR B WHERE B.ID = A.CAR_ID) PLATE_NUMBER,\n" +
//                "       A.CAR_ID,\n" +
                "       (SELECT C.NAME\n" +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                "          WHERE B.PARTY_ID = C.ID\n" +
                "            AND A.CREATOR_ID = B.ID) CREATOR_NAME,\n" +
                "       (SELECT B.NAME FROM MAS_DEPT B WHERE B.ID = A.DEPT_ID) DEPE_NAME,\n" +
                "       (SELECT B.DESCRIPTION\n" +
                "           FROM MAS_ENUMERATION B\n" +
                "          WHERE A.COMPLAIN_TYPE = B.ID) COMPLAIN_TYPE,\n" +
                "       (SELECT B.DESCRIPTION\n" +
                "           FROM MAS_ENUMERATION B\n" +
                "          WHERE A.COMPLAIN_CATEGORY = B.ID) COMPLAIN_CATEGORY, A.STATE,\n" +
                "       A.ACTIVE, A.CREATE_DATE, A.INITIAT_OA_DATE, A.TRANS_VISIT_DATE,\n" +
                "       A.TRANSTOP_DATE, A.STOP_DATE, A.REAL_START_DATE\n" +
                "  FROM FN_CUSTOM_COMPLAIN A\n" +
//                "  FROM CRM_CUSTOM_COMPLAIN A\n" +
                " WHERE A.DELETE_ID IS NULL\n" +
                "   AND A.END_ID IS NULL\n" +
                "   AND A.CUSTOM_COMPLAIN_TYPE = '000075'\n" +
                "   AND A.STATE NOT IN ('delete', 'end')";

        if( param.getString("companyId") != null &&  param.getJSONArray("companyId").size()!=0){
            sSQL += " AND EXISTS (SELECT 1 FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID AND D.COMPANY_ID IN ("+ translateArrayToString(param.getJSONArray("companyId"), ",") +"))";
        }

        return toJSONArray(this.find(sSQL));
    }

    /***
     * 功能： 获取车标
     * @param
     * @return
     */
    public JSONArray queryQQCB(String QCCP) {
        String sSQL = "SELECT T.LYDJLX, T.LYDJMC, T.LYDJHM, T.XH, T.CODING, T.TPNM, T.TPLJ\n" +
                "  FROM MAS_DJTPGL T\n" +
                " WHERE T.LYDJHM = :QCCP";
        JSONObject param = new JSONObject();
        param.put("QCCP",QCCP);
        return toJSONArray(this.find(sSQL,param));
    }

    /***
     * 功能： 为客诉总控获取节点耗时
     * @param
     * @return
     */
    public JSONArray queryConsumeTime(JSONObject param) {
        String sSQL = "SELECT TO_CHAR(B.BUSINESS_KEY_) BUSINESSKEY, (SELECT K.CREATE_TIME_\n" +
                "           FROM ACT_RU_TASK K\n" +
                "          WHERE B.PROC_INST_ID_ = K.PROC_INST_ID_) CREATEDATE,\n" +
                "       TO_CHAR((SELECT K.ID_\n" +
                "           FROM ACT_RU_TASK K\n" +
                "          WHERE B.PROC_INST_ID_ = K.PROC_INST_ID_)) TASKID, TO_CHAR(B.PROC_INST_ID_) PROCINSTID\n" +
                "  FROM ACT_RE_PROCDEF A, ACT_HI_PROCINST B\n" +
                " WHERE A.ID_ = B.PROC_DEF_ID_ AND A.NAME_ = '客诉单' AND B.BUSINESS_KEY_ = :ID";

        return toJSONArray(this.find(sSQL,param));
    }


    /***
     * 功能： 为客诉总控获取客诉单各个状态的数量以及月客诉总量
     * @param
     * @return
     */
    public JSONArray queryComplainNum(JSONObject param) {
        String sSQL = "";
        if( param.getString("companyId") != null &&  param.getJSONArray("companyId").size()!=0){
            String insertString = translateArrayToString(param.getJSONArray("companyId"), ",");

            sSQL = "SELECT NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.CREATE_DATE >= TRUNC(SYSDATE) AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) CREATE_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.STATE = 'create' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) WAIT_OA_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.STATE NOT IN ('delete', 'end') AND A.ACTIVE = 1 AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) WAIT_DEAL_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.STATE IN ('disposing') AND A.ACTIVE = 0 AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) STOP_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.STATE = 'visiting' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) END_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE A.COMPANY_ID IN ("+ insertString +") AND A.CREATE_DATE >= TRUNC(SYSDATE - 1) AND A.CREATE_DATE <= TRUNC(SYSDATE) AND A.STATE <> 'delete' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) YES_NUM\n" +
                    "FROM DUAL";
        } else {

            sSQL = "SELECT NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.CREATE_DATE >= TRUNC(SYSDATE) AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) CREATE_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.STATE = 'create' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) WAIT_OA_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.STATE NOT IN ('delete', 'end') AND A.ACTIVE = 1 AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) WAIT_DEAL_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.STATE IN ('disposing') AND A.ACTIVE = 0 AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) STOP_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.STATE = 'visiting' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) END_NUM,\n" +
                    "NVL((SELECT COUNT(*) FROM FN_CUSTOM_COMPLAIN A WHERE  A.CREATE_DATE >= TRUNC(SYSDATE - 1) AND A.CREATE_DATE <= TRUNC(SYSDATE) AND A.STATE <> 'delete' AND A.RESPONSIBILITY_COMPLAIN <> '000801'),0) YES_NUM\n" +
                    "FROM DUAL";
        }


        return toJSONArray(this.find(sSQL));
    }

    private String translateArrayToString(JSONArray array, String regex){
        String returnStr = "";
        for(int i =0;i < array.size();i++){
            if(i!= array.size()-1){
                returnStr = returnStr + array.getString(i)+ regex;
            }else{
                returnStr = returnStr  +array.getString(i);
            }
        }
        return returnStr;
    }

}
